package ac.manchester.cs.afzal.term_classifier.sql_handler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * Title: Term Classifier
 *
 * Description: A toolkit to find the relevance of candidate terms from a text corpus
 * to any particular topic/sub-domain using a term classification driven approach. The
 * system utilizes the lexical and contextual profiles of the candidate and domain-representing
 * "Resource Terms" (Seed and Ontological). The lexical and contextual profiles of candidate terms
 * are compared with those of Resource Terms to measure their relevance to the topic of interest.
 *
 * Derive_Distinct_Sentences class converts the
 * <p>Company: The University of Manchester</p>
 *
 * @author Hammad Afzal
 * @version 1.0
 */
public class SQL_Handler_Sentences_Impl extends SQL_Handler_Impl
{

    private Vector sen_ids = new Vector();
    private Vector<String> sen_values = new Vector();
    private Vector<String> d_sen_ids = new Vector();
    private Vector<String> d_doc_ids = new Vector();

    /**
     * Retrieves all Doc_IDs from Distinct_Sentences table
     * @return
     */
    public Vector get_all_documents_ids_from_distinct_Sentences()
    {
        d_doc_ids = new Vector();
        String query;
        try
        {
            query = "SELECT distinct Doc_ID FROM distinct_sentences";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                d_doc_ids.add(rs.getString("Doc_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Get list of all Corpus Documents from Distinct Sentences: " + sqlex.getMessage());
        }
        return d_doc_ids;
    }

    /*
     * It retrieves the IDs and Values of distinct senteces from Distinct_Sentences table.
     * @param doc_id
     */
    public Vector<String> get_all_distinct_sentences_per_doc(String doc_id)
    {
        Vector<String> sentence_values = new Vector();
        d_sen_ids.clear();
        String query;
        try
        {
            query = "SELECT D_Sen_ID,Value from distinct_sentences where Doc_ID = " + doc_id;
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                sentence_values.add(rs.getString("Value"));
                d_sen_ids.add(rs.getString("D_Sen_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Get Real Term Per Document: " + sqlex.getMessage());
        }
        return sentence_values;
    }

    /**
     * Returns all Doc_IDs from Sentences table
     * @return
     */
    public Vector<String> get_all_documents_ids_from_Sentences()
    {
        Vector<String> doc_ids = new Vector();

        try
        {
            String query = "SELECT distinct Doc_ID FROM Sentences";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                doc_ids.add(rs.getString("Doc_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Get list of all Corpus Documents: " + sqlex.getMessage());
        }
        return doc_ids;
    }

    /*
     * It retrieves the value of sentence id and sentence from Sentences table, when the id of a document is given.
     * @param doc_id
     */
    public Vector get_sentence_id_and_value_per_doc(String doc_id)
    {
        sen_values.clear();
        sen_ids.clear();

        try
        {
            statement = connection.createStatement();
            String query = "SELECT Sen_ID,Value FROM Sentences where Doc_ID =" + doc_id;
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                sen_values.add(rs.getString("Value"));
                sen_ids.add(rs.getInt("Sen_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Sentenec Value and Sentence IDs from Sentences when DocID is given : " + sqlex.getMessage());
        }
        return sen_values;
    }

    /*
     * It returns the distinct sentence id, when textual value of sentence and document id is given
     * as input.
     * @param value
     */
    public int get_distinct_sen_id_per_sentence_value_and_doc_id(String d_sen_value, String doc_id)
    {
        int sen_id = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT D_Sen_ID FROM Distinct_Sentences where Doc_ID = " + doc_id + " and Value = '" + d_sen_value + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                sen_id = rs.getInt("D_Sen_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Distinct Sentence ID when Value and Doc ID is given  : " + sqlex.getMessage());
        }
        return sen_id;
    }

    /*
     * It returns the distinct textual sentences for given document id.
     */
    public Vector<String> get_distinct_sentences_per_doc(String doc_id)
    {
        sen_values = new Vector();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT distinct Value FROM sentences where Doc_ID = " + doc_id;
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                sen_values.add(rs.getString("Value"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Sentenec Value from Sentences when DocID is given : " + sqlex.getMessage());
        }
        return sen_values;
    }

    /**
     * @return the sen_ids
     */
    public Vector get_sen_ids()
    {
        return sen_ids;
    }

    /**
     * @return the d_sen_ids
     */
    public Vector get_d_sen_ids()
    {
        return d_sen_ids;
    }

    /**
     * @return the d_doc_ids
     */
    public Vector get_d_doc_ids()
    {
        return d_doc_ids;
    }

    /**
     * @return the sen_values
     */
    public Vector get_sen_values()
    {
        return sen_values;
    }
}
